<?php

class Renew_Model_YachinSobaSearch extends Apm_Model_Base
{

    /**
     * 家賃相場表示日を取得
     *
     * @access public
     * @param array $params
     *            $timetype = 1→　今月, type = 2→　先月:
     * @return string yyyyMMdd
     */
    public function getYachinsoubaDate($timetype)
    {
        $sql = "";
        $todate = null;

        if ($timetype == 1)
        {
            $todate = date('Ymd');
        }
        else
        {
            $todate = date('Ymd', strtotime(date('Y-m-d') . " -1 month"));
        }

        if (APPLICATION_ENV == 'production')
        {
            // 本番は前日または前々日（日次で家賃相場が集計されるまでのブランクを保護）
            $sql .= "SELECT MAX(A02_TARGET_YMD) as YMD FROM A02ESTATE_MKT_PRICE_DAY30 WHERE A02_TARGET_YMD between " . ($todate - 2) . " and " . ($todate - 1) . "";
        }
        else
        {
            $sql .= "SELECT MAX(A02_TARGET_YMD) as YMD FROM A02ESTATE_MKT_PRICE_DAY30 WHERE A02_TARGET_YMD < " . $todate;
        }

        return $this->dbGetData($sql, array());
    }

    /**
     * 家賃相場を表示用に変換
     *
     * @access public
     * @param
     *            array ※参照渡し
     * @return void
     */
    public function convertYachinsouba($result, $lastMonthData, $params, &$data)
    {
        $maxPrice = array();
        $minPrice = array();
        $prefCd = $params['PrefCd'];

        $par_name = "";
        $soba = array();
        foreach ($result as $value)
        {
            $key = $prefCd . '-' . $value['KEY_CD'];
            $par_name = $value['PAR_NAME'];
            if (empty($soba[$key]))
            {
                $soba[$key] = array();
                $soba[$key]['KEY_CD'] = $key;
                $soba[$key]['PAR_NAME'] = $value['PAR_NAME'];
                $soba[$key]['KEY_NAME'] = $value['KEY_NAME'];
                $soba[$key]['SOBA'] = array();
            }
            $avg_data = array();
            $avg_data['YACHIN_AVG'] = sprintf("%.2f", $value['YACHIN_AVG'] / 10000);
            // $avg_data['MAX_FLG'] = 0;
            // $avg_data['MIN_FLG'] = 0;
            // $avg_data['5_DOWN'] = 0;
            $avg_data['CELLCLASS'] = "";

            if ($lastMonthData != null)
            {
                foreach ($lastMonthData as $value2)
                {
                    if ($value2['KEY_CD'] = $value['KEY_CD'])
                    {
                        if ($value2['YACHIN_AVG'] != 0 && ($value2['YACHIN_AVG'] - $value['YACHIN_AVG']) * 100 / $value2['YACHIN_AVG'] >= 5)
                        {
                            // $avg_data['5_DOWN'] = 1;
                            $avg_data['CELLCLASS'] = ' class="arr_down"';
                        }
                        break;
                    }
                }
            }

            $soba[$key]['SOBA'][$value['MADORI']] = $avg_data;

            // 最大値・最小値
            $ave = sprintf("%.9f", $value['YACHIN_AVG'] / 10000);
            if (empty($maxPrice[$value['MADORI']]))
            {
                $maxPrice[$value['MADORI']] = array(
                    $ave,
                    $key
                );
            }
            else if ($maxPrice[$value['MADORI']][0] < $ave)
            {
                $maxPrice[$value['MADORI']] = array(
                    $ave,
                    $key
                );
            }
            else if ($maxPrice[$value['MADORI']][0] == $ave)
            {
                array_push($maxPrice, $key);
            }
            if (empty($minPrice[$value['MADORI']]))
            {
                $minPrice[$value['MADORI']] = array(
                    $ave,
                    $key
                );
            }
            else if ($minPrice[$value['MADORI']][0] > $ave)
            {
                $minPrice[$value['MADORI']] = array(
                    $ave,
                    $key
                );
            }
            else if ($minPrice[$value['MADORI']][0] == $ave)
            {
                array_push($minPrice, $key);
            }
        }

        // 最大値・最小値フラグ立て
        foreach ($maxPrice as $key => $val)
        {
            if (count($val) > 1)
            {
                for ($i = 1; $i < count($val); $i++)
                {
                    // $soba[$val[$i]]['SOBA'][$key]['MAX_FLG'] = 1;
                    if ($soba[$val[$i]]['SOBA'][$key]['CELLCLASS'] == "") $soba[$val[$i]]['SOBA'][$key]['CELLCLASS'] = ' class="bg_pink"';
                }
            }
        }
        foreach ($minPrice as $key => $val)
        {
            if (count($val) > 1)
            {
                for ($i = 1; $i < count($val); $i++)
                {
                    // $soba[$val[$i]]['SOBA'][$key]['MIN_FLG'] = 1;
                    if ($soba[$val[$i]]['SOBA'][$key]['CELLCLASS'] == "") $soba[$val[$i]]['SOBA'][$key]['CELLCLASS'] = ' class="bg_green"';
                }
            }
        }

        $data['yachinsouba'] = $soba;
        $data['PAR_NAME'] = $par_name;
    }

    /**
     * 家賃相場（市区町村）データ取得
     */
    public function getYachinsoubaCityData($params, $timetype, $ymd)
    {
        $db_params = array();
        $sql = $this->generateYachinsoubaCitySql($params, $db_params, $timetype, $ymd);

        return $this->dbGetList($sql, $db_params);
    }

    /**
     * 家賃相場（市区町村）データ検索用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     *
     * @access public
     * @param
     *            array
     * @param
     *            array
     * @return string 家賃相場（沿線）データ検索用SQL
     */
    public function generateYachinsoubaCitySql($params, &$db_params, $timetype, $ymd)
    {
        // 表示日を取得
        // $ymd = $this->getYachinsoubaDate($timetype);
        if (!empty($ymd))
            $db_params['ymd'] = $ymd['YMD'];
        else $db_params['ymd'] = date('Ymd') - 1;

        $sql = "";
        $sql .= "SELECT";
        $sql .= "   A02.A02_KEY_CD as KEY_CD ";
        $sql .= " , M01.M01_TODOUFUKEN   as PAR_NAME ";
        $sql .= " , M01.M01_SHIKUCHOUSON as KEY_NAME ";
        $sql .= " , A02.MADORI as MADORI ";
        $sql .= " , avg(A02.A02_YACHIN_AVG) as YACHIN_AVG ";
        $sql .= "FROM";
        $sql .= " ( SELECT";
        $sql .= "      A02_KEY_CD ";
        $sql .= "    , case";
        $sql .= "        when A02_MADORI_CD in (10) THEN '1R'";
        $sql .= "        when A02_MADORI_CD in (11) THEN '1K'";
        $sql .= "        when A02_MADORI_CD in (12,13) THEN '1DK'";
        $sql .= "        when A02_MADORI_CD in (14,15) THEN '1LDK'";
        $sql .= "        when A02_MADORI_CD in (20,21) THEN '2K'";
        $sql .= "        when A02_MADORI_CD in (22,23) THEN '2DK'";
        $sql .= "        when A02_MADORI_CD in (24,25) THEN '2LDK'";
        $sql .= "        when A02_MADORI_CD in (30,31) THEN '3K'";
        $sql .= "        when A02_MADORI_CD in (32,33) THEN '3DK'";
        $sql .= "        when A02_MADORI_CD in (34,35) THEN '3LDK'";
        $sql .= "        when A02_MADORI_CD in (40,41) THEN '4K'";
        $sql .= "        when A02_MADORI_CD in (42,43) THEN '4DK'";
        $sql .= "        when A02_MADORI_CD in (44,45) THEN '4LDK'";
        $sql .= "        when A02_MADORI_CD > 49 and A02_MADORI_CD < 80 THEN '5KKARA'";
        // $sql .= " else '99'";
        $sql .= "      end MADORI ";
        $sql .= "    , A02_YACHIN_AVG ";
        $sql .= "   FROM A02ESTATE_MKT_PRICE_DAY30 ";
        $sql .= "   WHERE A02_DATA_KUBUN = 11 ";
        $sql .= "     AND A02_TARGET_YMD = :ymd ";
        $sql .= "     AND SUBSTR(A02_KEY_CD,1,2) = :PrefCd ";

        // マンション
        if ($params['type'] == '101')
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD IN (2)";
            // 賃貸アパート
        }
        else if ($params['type'] == '102')
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD IN (1,3,4,10)";
            // 戸建て・テラスハウス
        }
        else if ($params['type'] == '103')
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD IN (5,6,7,8,9)";
        }
        else
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD = -1 ";
        }

        // 間取り　一人暮らし
        if ($params['madori'] == '201')
        {
            $sql .= " AND A02_MADORI_CD IN (10,11,12,13,14,15,20,21,22,23) ";
        }
        // 間取り　二人暮らし
        else if ($params['madori'] == '202')
        {
            $sql .= " AND A02_MADORI_CD IN (14,15,20,21,22,23,24,25,30,31,32,33,34,35)";
        }
        // 間取り　ファミリー
        else if ($params['madori'] == '203')
        {
            $sql .= " AND A02_MADORI_CD > 23 AND A02_MADORI_CD < 80 ";
        }
        else
        {
            $sql .= " AND A02_MADORI_CD = -1 ";
        }

        $sql .= " ) A02 ";
        $sql .= ",( SELECT DISTINCT";
        $sql .= "      M01_TODOUFUKEN_CD ";
        $sql .= "    , M01_TODOUFUKEN ";
        $sql .= "    , M01_SHIKUCHOUSON_CD ";
        $sql .= "    , M01_SHIKUCHOUSON ";
        $sql .= "   FROM M01JYUUSHO ";
        $sql .= "   WHERE M01_TODOUFUKEN_CD = :PrefCd ";
        $sql .= " ) M01 ";
        $sql .= "WHERE  ";
        $sql .= "  A02.A02_KEY_CD = M01.M01_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD ";
        $sql .= "GROUP BY";
        $sql .= "   A02.A02_KEY_CD ";
        $sql .= " , M01.M01_TODOUFUKEN ";
        $sql .= " , M01.M01_SHIKUCHOUSON ";
        $sql .= " , A02.MADORI ";
        $sql .= "ORDER BY";
        $sql .= " A02.A02_KEY_CD ";
        $sql .= " asc ";

        $db_params['PrefCd'] = $params['PrefCd'];

        return $sql;
    }

    /**
     * 路線データ取得
     */
    public function getEnsenDataByPrefCd($params)
    {
        $db_params = array();
        $db_params['PrefCd'] = $params['PrefCd'];

        $ymd = $this->getYachinsoubaDate(1);

        if (!empty($ymd))
            $db_params['ymd'] = $ymd['YMD'];
        else $db_params['ymd'] = date('Ymd') - 1;

        $sql = $this->generateEnsenByPrefCdSql($params);
        return $this->dbGetList($sql, $db_params);
    }

    /**
     * 表示用変換
     *
     * @param
     *            $railList
     * @param
     *            $params
     * @return 変換後リスト
     */
    public function convertEnsenDataList($railList)
    {
        $DispList = array();
        $indexNo = 0;
        $beforeEnsenKbn = null;

        foreach ($railList as $railData)
        {
            $ensenKbn = $railData['ENSEN_KBN'];

            // 沿線区分が異なる場合
            if ($beforeEnsenKbn != $ensenKbn)
            {
                $ensenKbnName = Apm_ShopUtil::getEnsenKbnName($ensenKbn);

                $DispList["$ensenKbn"]['EnsenKbnName'] = $ensenKbnName;
                $DispList["$ensenKbn"]['RailList'] = array();
            }

            $DispList["$ensenKbn"]['RailList'][] = $railData;
            $beforeEnsenKbn = $ensenKbn;
        }

        return $DispList;
    }

    function generateEnsenByPrefCdSql($params)
    {
        $sql = "";
        $sql .= " SELECT";
        $sql .= "	M02_ENSEN_CD AS ENSEN_CD ";
        $sql .= "	,M02_ENSEN_NAME AS ENSEN_NAME";
        $sql .= "	,M02_ENSEN_KANA AS ENSEN_KANA";
        $sql .= "	,M02_ENSEN_KUBUN AS ENSEN_KBN ";
        $sql .= " FROM ";
        $sql .= "   ( SELECT DISTINCT M03_ENSEN_CD ";
        $sql .= "      FROM A02ESTATE_MKT_PRICE_DAY30 A02,  ";
        $sql .= "	        M03EKI M03";
        $sql .= "     WHERE A02.A02_DATA_KUBUN = 21 ";
        $sql .= "      AND A02_TARGET_YMD = :ymd ";
        $sql .= "      AND A02.A02_KEY_CD = M03.M03_EKI_CD ";
        $sql .= "	   AND M03.M03_TODOUFUKEN_CD = :PrefCd";

        // マンション
        if ($params['type'] == '101')
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD IN (2)";
            // 賃貸アパート
        }
        else if ($params['type'] == '102')
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD IN (1,3,4,10)";
            // 戸建て・テラスハウス
        }
        else if ($params['type'] == '103')
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD IN (5,6,7,8,9)";
        }
        else
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD = -1 ";
        }

        // 間取り　一人暮らし
        if ($params['madori'] == '201')
        {
            $sql .= " AND A02_MADORI_CD IN (10,11,12,13,14,15,20,21,22,23) ";
        }
        // 間取り　二人暮らし
        else if ($params['madori'] == '202')
        {
            $sql .= " AND A02_MADORI_CD IN (14,15,20,21,22,23,24,25,30,31,32,33,34,35)";
        }
        // 間取り　ファミリー
        else if ($params['madori'] == '203')
        {
            $sql .= " AND A02_MADORI_CD > 23 AND A02_MADORI_CD < 80 ";
        }
        else
        {
            $sql .= " AND A02_MADORI_CD = -1 ";
        }
        // $sql .= " FROM ";
        // $sql .= " ( SELECT DISTINCT M03_ENSEN_CD ";
        // $sql .= " FROM M03EKI";
        // $sql .= " WHERE M03_TODOUFUKEN_CD = :PrefCd";
        $sql .= "	 ) E ";
        $sql .= "	, M02ENSEN M02";
        $sql .= " WHERE M02.M02_ENSEN_CD = E.M03_ENSEN_CD ";
        $sql .= " ORDER BY M02_ENSEN_KUBUN ";
        return $sql;
    }

    /**
     * 家賃相場（沿線）データ取得
     */
    public function getYachinsoubaEnsenData($params, $timetype, $ymd)
    {
        $db_params = array();
        $sql = $this->generateYachinsoubaEnsenSql($params, $db_params, $timetype, $ymd);

        return $this->dbGetList($sql, $db_params);
    }

    /**
     * 家賃相場（沿線）データ検索用SQL文の取得
     *
     * 沿線一覧データ検索用SQL文の取得を行う
     *
     * @access public
     * @param
     *            array
     * @param
     *            array
     * @return string 家賃相場（沿線）データ検索用SQL
     */
    public function generateYachinsoubaEnsenSql($params, &$db_params, $timetype, $ymd)
    {
        if (!empty($ymd))
            $db_params['ymd'] = $ymd['YMD'];
        else $db_params['ymd'] = date('Ymd') - 1;

        $sql = "";
        $sql = "SELECT ";
        $sql .= "     A02_KEY_CD as KEY_CD ";
        $sql .= "    , M03_ENSEN_NAME as PAR_NAME ";
        $sql .= "    , M03_EKI_NAME   as KEY_NAME ";
        $sql .= "    , MADORI ";
        $sql .= "    , avg(A02_YACHIN_AVG) as YACHIN_AVG ";

        $sql .= " FROM ( SELECT ";
        $sql .= "     A02_KEY_CD ";
        $sql .= "    , M03.M03_ENSEN_NAME ";
        $sql .= "    , M03.M03_EKI_NAME ";
        $sql .= "    , A02_YACHIN_AVG ";
        $sql .= "    , case";
        $sql .= "        when A02_MADORI_CD in (10) THEN '1R'";
        $sql .= "        when A02_MADORI_CD in (11) THEN '1K'";
        $sql .= "        when A02_MADORI_CD in (12,13) THEN '1DK'";
        $sql .= "        when A02_MADORI_CD in (14,15) THEN '1LDK'";
        $sql .= "        when A02_MADORI_CD in (20,21) THEN '2K'";
        $sql .= "        when A02_MADORI_CD in (22,23) THEN '2DK'";
        $sql .= "        when A02_MADORI_CD in (24,25) THEN '2LDK'";
        $sql .= "        when A02_MADORI_CD in (30,31) THEN '3K'";
        $sql .= "        when A02_MADORI_CD in (32,33) THEN '3DK'";
        $sql .= "        when A02_MADORI_CD in (34,35) THEN '3LDK'";
        $sql .= "        when A02_MADORI_CD in (40,41) THEN '4K'";
        $sql .= "        when A02_MADORI_CD in (42,43) THEN '4DK'";
        $sql .= "        when A02_MADORI_CD in (44,45) THEN '4LDK'";
        $sql .= "        when A02_MADORI_CD > 49 and A02_MADORI_CD < 80 THEN '5KKARA'";
        $sql .= "      end MADORI ";

        $sql .= "   FROM A02ESTATE_MKT_PRICE_DAY30, M03EKI M03 ";
        $sql .= "     WHERE A02_DATA_KUBUN = 21 ";
        $sql .= "      AND A02_TARGET_YMD = :ymd ";
        $sql .= "      AND A02_KEY_CD = M03.M03_EKI_CD ";
        $sql .= "      AND M03.M03_ENSEN_CD = :RailCd ";
        $sql .= "	   AND M03.M03_TODOUFUKEN_CD = :PrefCd ";

        // マンション
        if ($params['type'] == '101')
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD IN (2)";
            // 賃貸アパート
        }
        else if ($params['type'] == '102')
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD IN (1,3,4,10)";
            // 戸建て・テラスハウス
        }
        else if ($params['type'] == '103')
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD IN (5,6,7,8,9)";
        }
        else
        {
            $sql .= " AND A02_TATEMONO_TYPE_CD = -1 ";
        }

        // 間取り　一人暮らし
        if ($params['madori'] == '201')
        {
            $sql .= " AND A02_MADORI_CD IN (10,11,12,13,14,15,20,21,22,23) ";
        }
        // 間取り　二人暮らし
        else if ($params['madori'] == '202')
        {
            $sql .= " AND A02_MADORI_CD IN (14,15,20,21,22,23,24,25,30,31,32,33,34,35)";
        }
        // 間取り　ファミリー
        else if ($params['madori'] == '203')
        {
            $sql .= " AND A02_MADORI_CD > 23 AND A02_MADORI_CD < 80 ";
        }
        else
        {
            $sql .= " AND A02_MADORI_CD = -1 ";
        }
        $sql .= " ) DATA ";

        $sql .= " GROUP BY ";
        $sql .= "   A02_KEY_CD ";
        $sql .= " , M03_ENSEN_NAME ";
        $sql .= " , M03_EKI_NAME ";
        $sql .= " , MADORI ";

        $sql .= " ORDER BY A02_KEY_CD ASC ";

        $db_params['PrefCd'] = $params['PrefCd'];
        $db_params['RailCd'] = $params['RailCd'][0];

        return $sql;
    }

}
?>